<?php

class multitender_model_subscribe extends multitender_model {
    
    /**
     * Работаем с базой Personal
     */
    public function __construct() {
        parent::__construct();
        $this->db      = & $this->conf['dbs']['person'];        
    }

    /**
     * Возвращает список запросов пользователей рассылка для которых была обработана $date
     * @param date $date
     * @return array
     */
    public function GetUserSearches($date) {
        $sql = "SELECT E.user_id, S.search FROM email_subscribe as E 
                LEFT JOIN search as S ON S.id = E.search_id
                WHERE DATE(E.`datetime`) = ? AND E.`active` = 1";
        $return = $this->db->GetAll($sql, array($date));
        return $return;
    }
    
    /**
     * Возвращает количество новых запросов добавленных $date
     * @param type $date
     * @return int
     */
    public function GetNewSearch($date) {
        $sql = "SELECT count(*) FROM email_subscribe WHERE DATE(dateadd) = ?";
        $return = $this->db->GetOne($sql, array($date));
        return $return;
    }
    
    public function GetNewUsers($date) {
        $sql = "SELECT count(*) FROM (
                    SELECT user_id, MIN(DATE(dateadd)) as da FROM email_subscribe GROUP BY user_id HAVING da = ? ORDER BY user_id
                ) as U";
        $return = $this->db->GetOne($sql, array($date));
        return $return;
    }
    
    public function GetCountUserActive() {
        $sql = "SELECT COUNT(*) FROM (SELECT MAX(active) as a FROM email_subscribe GROUP BY user_id HAVING a = 1) as U";
        $return = $this->db->GetOne($sql);
        return $return;        
    }


    /**
     * Возвращает количество уникальных пользователей, которым была выслана подписка по запросам, 
     * в период с $datestart по $datefinal    
     * @param datetime $datestart
     * @param datetime $datefinal
     * @return integer
     */
    public function GetCountRangeTime($datestart, $datefinal) {       
        $datestart = date("Y-m-d H:i:s", $datestart);
        $datefinal = date("Y-m-d H:i:s", $datefinal);        
        $sql = "SELECT COUNT(*) FROM email_subscribe WHERE datetime >= ? AND datetime < ? AND active = 1";
        $count = $this->db->GetOne($sql, array($datestart, $datefinal));        
        return (int) $count;
    }
    
    /**
     * Возвращает количество уникальных пользователей, которые были подписаны на рассылку меньше ранее чем $date
     * @param datetime $date
     * @return integer
     */
    public function GetUsersCount($date = false) {        
        $sql = "SELECT user_id, MIN(dateadd) as mda FROM email_subscribe GROUP BY user_id";
        if ($date) { $sql.=" HAVING mda < ?"; $date = date("Y-m-d H:i:s", $date); } 
        $sql = "SELECT COUNT(*) as CNT FROM ($sql) as M";
        $count = $this->db->GetOne($sql, array($date));
        return $count;
    }
        
    /**
     * Возвращает список запросов по которым числа $date была произведена рассылка       
     * @return array
     */
    public function GetSearchesUsers($date) {
        
        $ds = $date . " 00:00:00";
        $de = $date . " 23:59:59";
        
        $sql = "SELECT E.user_id, S.search FROM email_subscribe as E"
              ." LEFT JOIN search as S ON S.id = E.search_id "
              ." WHERE E.`active` = 1 AND E.`datetime` >= ? AND E.`datetime` <= ?"
              ." ORDER BY E.user_id";        
        
        $searchs = $this->db->GetAll($sql, array($ds, $de));
        return $searchs;
    }
        
    /**
     * Возвращает запрос по его идентификатору $search
     * @param integer $search_id
     * @return array
     */
    public function GetSearch($search_id) {
        $sql = "SELECT * FROM search WHERE id = ?";
        return $this->db->GetRow($sql, array($search_id));
    }        
    
    public function GetFreeSub($arr) {
        $sql = "SELECT users.name, COUNT(user_id) as c FROM email_subscribe, users WHERE users.Id = email_subscribe.user_id AND search_id IN (".  implode(",", $arr).") GROUP BY user_id ";
        return $this->db->GetAll($sql);
    }
        
    
}